using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using MySql.Data.MySqlClient;
using System.Data;
using System.Configuration;

namespace TestProject
{
	/// <summary>
	/// Class representing a book in the database. 
	/// </summary>
	public class Book
	{
		private int m_book_ID = 0;
		private string m_isbn;
		private string m_title;
		private string m_author;
		private string m_image;
		private string m_synopsis;
		private string m_publisher;
		
		public Book()
		{
		}
		
		public Book(int book_ID)
		{
			if (book_ID!=0)
			{
				this.m_book_ID = book_ID;
				getData();
			}
		}
		
		public int book_ID
		{
			set {
				m_book_ID = value;
				getData();
			}
			get {
				return m_book_ID;
			}
		}
		
		public string isbn
		{
			set {
				m_isbn = value;
			}
			get {
				return m_isbn;
			}
		}
		
		public string title
		{
			set {
				m_title = value;
			}
			get {
				return m_title;
			}
		}
		
		public string author
		{
			set {
				m_author = value;
			}
			get {
				return m_author;
			}
		}
		
		public string image
		{
			set {
				m_image = value;
			}
			get {
				return m_image;
			}
		}
		
		public string synopsis
		{
			set {
				m_synopsis = value;
			}
			get {
				return m_synopsis;
			}
		}
		
		public string publisher
		{
			set {
				m_publisher = value;
			}
			get {
				return m_publisher;
			}
		}
		
		private void getData()
		{
	        MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT isbn, title, author, image, synopsis, publisher "
					+ "FROM books WHERE book_id = '" + this.m_book_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					m_isbn = (string) reader["isbn"];
					m_title = (string) reader["title"];
					m_author = (string) reader["author"];
					m_image = (string) reader["image"];
					m_synopsis = (string) reader["synopsis"];
					m_publisher = (string) reader["publisher"];
		        }
				reader.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Gets the number of copies of the book stored in the database. 
		/// </summary>
		/// <returns>
		/// A <see cref="System.Int32"/>
		/// </returns>
		public int getNumberOfCopies()
		{
			int toReturn = 0;
			if (m_book_ID!=0)
			{
				MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	       		try
				{
					conn.Open();
					string statement = "SELECT COUNT(*) FROM copy WHERE book_ID='"+m_book_ID+"'";
		   	     	MySqlCommand cmd = new MySqlCommand(statement, conn);			
		    	    Object result = cmd.ExecuteScalar();
					if (result != null)
					{
						toReturn = Convert.ToInt32(result);
					}
				}
				catch(Exception e)
				{
					Console.WriteLine(e.ToString());
				}
		    	conn.Close();
			}
			return toReturn;
		}
		
		public List<Copy> getCopies()
		{
			List<Copy> copyList = new List<Copy>(0);
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			try
			{
				conn.Open();
				string statement = "SELECT copy_id FROM copy WHERE book_id = '"+this.m_book_ID+"'";
				 MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					copyList.Add(new Copy((int)reader["copy_id"]));
		        }
				reader.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return copyList;
		}
		
		/// <summary>
		/// Gets the genres of the book. 
		/// </summary>
		/// <returns>
		/// A <see cref="List<Genre>"/> The genres in a List<Genre> object.
		/// </returns>
		public List<Genre> getGenres()
		{
			List<Genre> genreList = new List<Genre>(0);						
	        MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT genre_id "
					+ "FROM bookgenre WHERE book_id = '" + this.m_book_ID + "'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					genreList.Add(new Genre((int)reader["genre_id"]));
		        }
				reader.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return genreList;
		}
		
		/// <summary>
		/// Adds a list of genres to the genre of the book. 
		/// </summary>
		/// <param name="genres">
		/// A <see cref="List<Genre>"/> The genres in a List<Genre> object.
		/// </param>
		public void addGenre(List<Genre> genres)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				for (int i=0; i<genres.Count; i++)
				{
					string newStatement = "INSERT INTO bookgenre (book_id, genre_id) "
						+ "VALUES ('"+m_book_ID+"','"+genres[i].genre_ID+"')";
			        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
					newCmd.ExecuteNonQuery();
				}
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();	
		}
		
		/// <summary>
		/// Updates the genres assigned to a book. 
		/// </summary>
		/// <param name="genres">
		/// A <see cref="List<Genre>"/> The genres to be assigned to the book, in a List<Genre> object.
		/// </param>
		public void updateGenres(List<Genre> genres)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "DELETE FROM bookgenre where book_ID='"+m_book_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);
				cmd.ExecuteNonQuery();
				for (int i=0; i<genres.Count; i++)
				{
					string newStatement = "INSERT INTO bookgenre (book_id, genre_id) "
						+ "VALUES ('"+m_book_ID+"','"+genres[i].genre_ID+"')";
			        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
					newCmd.ExecuteNonQuery();
				}
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();			
		}
		
		/// <summary>
		/// Adds a book to the database. 
		/// </summary>
		/// <param name="isbn">
		/// A <see cref="System.String"/> The ISBN of the book.
		/// </param>
		/// <param name="title">
		/// A <see cref="System.String"/> The title of the book.
		/// </param>
		/// <param name="author">
		/// A <see cref="System.String"/> The author(s) of the book.
		/// </param>
		/// <param name="image">
		/// A <see cref="System.String"/> The path for the image file for the book cover.
		/// </param>
		/// <param name="synopsis">
		/// A <see cref="System.String"/> The synopsis of the book.
		/// </param>
		/// <param name="publisher">
		/// A <see cref="System.String"/> The publisher of the book.
		/// </param>
		/// <returns>
		/// A <see cref="Book"/>
		/// </returns>
		public static Book addBook(string isbn, string title, string author, string image, string synopsis, string publisher)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			Book toReturn = null;
	        try
			{
				conn.Open();
				string newStatement = "INSERT INTO books (isbn, title, author, image, synopsis, publisher) "
					+ "VALUES ('"+isbn+"','"+title+"','"+author+"','"+image+"','"+synopsis+"','"+publisher+"')";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
				string statement = "SELECT book_ID FROM books WHERE isbn='"+isbn+"' AND title='"+title+"' AND author='"+author+"' AND image='"+image+"' AND synopsis='"+synopsis+"' AND publisher='"+publisher+"' ORDER BY book_ID ASC";
				MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
				toReturn = new Book();
		        while(reader.Read()) {
					toReturn.book_ID = (int)reader["book_ID"];
				}
				reader.Close();				
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return toReturn;
		}
		
		/// <summary>
		/// Edits a book's information. 
		/// </summary>
		/// <param name="book_ID">
		/// A <see cref="System.Int32"/> The corresponding book_ID of the book.
		/// </param>
		/// <param name="isbn">
		/// A <see cref="System.String"/> The ISBN of the book.
		/// </param>
		/// <param name="title">
		/// A <see cref="System.String"/> The title of the book.
		/// </param>
		/// <param name="author">
		/// A <see cref="System.String"/> The author(s) of the book.
		/// </param>
		/// <param name="image">
		/// A <see cref="System.String"/> The path for the image file of the book.
		/// </param>
		/// <param name="synopsis">
		/// A <see cref="System.String"/> The synopsis of the book.
		/// </param>
		/// <param name="publisher">
		/// A <see cref="System.String"/> The publisher of the book.
		/// </param>
		public static void editBook(int book_ID, string isbn, string title, string author,  string synopsis, string publisher)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string newStatement = "UPDATE books SET isbn='"+isbn+"', title='"+title+"', author='"+author+"', synopsis='"+synopsis+"', publisher='"+publisher
					+ "' WHERE book_ID='"+book_ID+"'";
		        MySqlCommand newCmd = new MySqlCommand(newStatement, conn);
				newCmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Returns all the books stored in the database. The books are returned in a List<Book> object.
		/// </summary>
		/// <returns>
		/// A <see cref="List<Book>"/> The books in a List<Book> object.
		/// </returns>
		public static List<Book> getBooks()
		{
			List<Book> booksList = new List<Book>(0);
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT book_id "
					+ "FROM books";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					Book current = new Book((int) reader["book_id"]);
					booksList.Add(current);
		        }
				reader.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return booksList;
		}
		
		/// <summary>
		/// Deletes a book, given its book_ID. 
		/// </summary>
		/// <param name="book_ID">
		/// A <see cref="System.Int32"/> The book_ID of the book to be deleted.
		/// </param>
		public static void deleteBook(int book_ID)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();			
				foreach(Copy item in (Book.getCopies(book_ID) as List<Copy>))
				{
					Copy.deleteCopy(item.copy_ID);
				}	
				string statement = "DELETE FROM books where book_ID='"+book_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);
				cmd.ExecuteNonQuery();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
		}
		
		/// <summary>
		/// Gets the copies of the books in the database. 
		/// </summary>
		/// <param name="book_ID">
		/// A <see cref="System.Int32"/> The book_ID of the book.
		/// </param>
		/// <returns>
		/// A <see cref="List<Copy>"/> The copies of the book in a List<Copy> object.
		/// </returns>
		public static List<Copy> getCopies(int book_ID)
		{
			List<Copy> copiesList = new List<Copy>(0);
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT copy_ID "
					+ "FROM copy WHERE book_ID='"+book_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					Copy current = new Copy((int) reader["copy_id"]);
					copiesList.Add(current);
		        }
				reader.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return copiesList;
		}
		
		public static List<Book> filtersearchedBooks(int index, List<Book> book)
		{
			int max = index+10;
			if(max > book.Count)
			{
				max = book.Count;
			}
			List<Book> filteredBooks = new List<Book>(0);
			
			for(int i = index; i < max ; i++)
			{
				filteredBooks.Add(book[i]);
			}
			return filteredBooks;
		}
		
		/// <summary>
		/// Searches the list of books provided using the searchString provided. 
		/// </summary>
		/// <param name="searchString">
		/// A <see cref="System.String"/> The search string.
		/// </param>
		/// <param name="books">
		/// A <see cref="List<Book>"/> The list of books to search from.
		/// </param>
		/// <returns>
		/// A <see cref="List<Book>"/> The list of books that matched the search string.
		/// </returns>
		public static List<Book> searchBooks(string searchString, List<Book> books)
		{			
			List<Book> booksList = new List<Book>(0);
			for (int i=0; i<books.Count; i++)
			{
				if (books[i].isbn.ToLower().Contains(searchString.ToLower()) || books[i].title.ToLower().Contains(searchString.ToLower()) || books[i].author.ToLower().Contains(searchString.ToLower()) || books[i].synopsis.ToLower().Contains(searchString.ToLower()) || books[i].publisher.ToLower().Contains(searchString.ToLower()))
					booksList.Add(books[i]);
			}
			return booksList;
		}
		
		public static Book searchBookByISBN(string isbn, List<Book> books)
		{
			for(int i= 0; i< books.Count; i++)
			{
				if(books[i].isbn.ToLower().Contains(isbn.ToLower()))
				{
					return books[i];
				}
			}
			return null;
		}
		
		public static List<Copy> getAvailableCopies(int book_ID)
		{
			List<Copy> copiesList = new List<Copy>(0);
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT copy_ID "
					+ "FROM copy WHERE book_ID='"+book_ID+"'"
					+ " AND copy_ID NOT IN (SELECT copy_ID FROM loan WHERE datereturned is null)";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					Copy current = new Copy((int) reader["copy_id"]);
					copiesList.Add(current);
		        }
				reader.Close();
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return copiesList;			
		}
		
		public static List<Loan> getUnreturnedBooks()
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			List<Loan> toReturn = new List<Loan>(0);
	        try
			{
				conn.Open();
				string statement = "SELECT loan_ID "
					+ "FROM loan WHERE datereturned is null";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					toReturn.Add(new Loan((int) reader["loan_ID"]));
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return toReturn;			
		}
		
		public static List<Reservation> getReservations(int book_ID)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
			List<Reservation> toReturn = new List<Reservation>(0);
	        try
			{
				conn.Open();
				string statement = "SELECT reservation_ID "
					+ "FROM reservation WHERE book_id ='"+book_ID+"' ORDER BY reservation_date ASC";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        while(reader.Read()) {
					toReturn.Add(new Reservation((int) reader["reservation_ID"]));
		        }
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
			}
		    conn.Close();
			return toReturn;		
		}
		
		public static bool isReserved(int book_ID, int user_ID)
		{
			MySqlConnection conn = new MySqlConnection(ConfigurationManager.ConnectionStrings["MySQL"].ConnectionString);
	        try
			{
				conn.Open();
				string statement = "SELECT reservation_ID "
					+ "FROM reservation WHERE book_id ='"+book_ID+"' AND user_id='"+user_ID+"'";
		        MySqlCommand cmd = new MySqlCommand(statement, conn);			
		        MySqlDataReader reader = cmd.ExecuteReader();
		        if (reader.HasRows)
				{
					conn.Close();
					return true;
				}
				else
				{
					conn.Close();
					return false;
				}
			}
			catch(Exception e)
			{
				Console.WriteLine(e.ToString());
				return false;
			}
		}
	}
}